﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_FIM_MoneyReturnDtls : IDAL_FIM_MoneyReturnDtls
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        

        public D_FIM_MoneyReturnDtls()
        {

        }

        public IList<M_FIM_MoneyReturnDtls> GetFull(Nullable<int> return_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyReturnDtls> infoList = new List<M_FIM_MoneyReturnDtls>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@return_seq", return_seq);

                strSQL = "SELECT red.return_dtls_seq, red.return_seq, red.money_req_dtls_seq, red.amount, red.vat_amount"

                    + ", mrd.money_req_dtls_name, mrd.amount as money_req_dtls_amount"
                    + ", exp.bgm_exp_name"

                    + ", red.record_status"
                    + ", red.created_by, red.created_when, red.updated_by, red.updated_when"
                    + ", red.ConId, red.ref_no, red.ref_date, c.NameTh, red.branch_no"
                    + " FROM fim_money_return_dtls red"
                    + " LEFT JOIN fim_money_request_dtls mrd ON red.money_req_dtls_seq = mrd.money_req_dtls_seq"
                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN IcmMasContact c on red.ConId = c.ConId"
                    
                    + " WHERE red.record_status not like @delete_status"
                    + (return_seq != null ? " and red.return_seq = @return_seq" : "");
                //    + " ORDER BY mrd.money_req_dtls_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyReturnDtls info = new M_FIM_MoneyReturnDtls();
                        info.return_dtls_seq = cn.ConvertNullInt(drc[i]["return_dtls_seq"]);
                        info.return_seq = cn.ConvertNullInt(drc[i]["return_seq"]);
                        info.money_req_dtls_seq = cn.ConvertNullInt(drc[i]["money_req_dtls_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.vat_amount = cn.ConvertNullDouble(drc[i]["vat_amount"]);

                        info.money_req_dtls_name = drc[i]["money_req_dtls_name"].ToString();
                        info.req_amount = cn.ConvertNullDouble(drc[i]["money_req_dtls_amount"]);
                        info.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        info.ConId = cn.ConvertNullInt(drc[i]["ConId"]);
                        info.contact_name = drc[i]["NameTh"].ToString();
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.ref_date = cn.ConvertNullDateTime(drc[i]["ref_date"]);
                        info.branch_no = drc[i]["branch_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MoneyReturnDtls> GetMoneyReqDtls(Nullable<int> return_seq
            , Nullable<int> money_req_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyReturnDtls> infoList = new List<M_FIM_MoneyReturnDtls>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@return_seq", return_seq);
                dbManager.AddParameters(2, "@money_req_seq", money_req_seq);

                strSQL = "SELECT mrd.money_req_dtls_seq, red.return_dtls_seq, red.return_seq"

                    + ", red.amount as return_amount, red.vat_amount, red2.accum_amount"

                    + ", mrd.amount as req_amount"

                    + ", mrd.money_req_dtls_name, mrd.remarks, red.ConId, exp.bgm_exp_name"

                    + ", red.record_status"
                    + ", red.created_by, red.created_when, red.updated_by, red.updated_when"

                    + ", red.ConId, red.ref_no, red.ref_date, c.NameTh, red.branch_no"

                    + " FROM fim_money_request_dtls mrd"
                    + " LEFT JOIN fim_money_return_dtls red ON mrd.money_req_dtls_seq = red.money_req_dtls_seq"
                        + (return_seq != null ? " and red.return_seq = @return_seq" : "")
                    + " LEFT JOIN (select money_req_dtls_seq"
                        + ", sum(coalesce(amount, 0)) as accum_amount"
                        + " from fim_money_return_dtls"
                        + " where record_status not like @delete_status"
                        + (return_seq != null ? " and return_seq <> @return_seq" : "")
                        + " group by money_req_dtls_seq"
                        + ") red2 on mrd.money_req_dtls_seq = red2.money_req_dtls_seq"
                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN IcmMasContact c on red.ConId = c.ConId"

                    + " WHERE mrd.record_status not like @delete_status"
                    + (money_req_seq != null ? " and mrd.money_req_seq = @money_req_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyReturnDtls info = new M_FIM_MoneyReturnDtls();

                        info.return_dtls_seq = cn.ConvertNullInt(drc[i]["return_dtls_seq"]);
                        info.return_seq = cn.ConvertNullInt(drc[i]["return_seq"]);
                        info.money_req_dtls_seq = cn.ConvertNullInt(drc[i]["money_req_dtls_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["return_amount"]);
                        info.vat_amount = cn.ConvertNullDouble(drc[i]["vat_amount"]);
                        info.accum_amount = cn.ConvertNullDouble(drc[i]["accum_amount"]);

                        info.money_req_dtls_name = drc[i]["money_req_dtls_name"].ToString();
                        info.req_amount = cn.ConvertNullDouble(drc[i]["req_amount"]);
                        info.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        info.ConId = cn.ConvertNullInt(drc[i]["ConId"]);
                        info.contact_name = drc[i]["NameTh"].ToString();
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.ref_date = cn.ConvertNullDateTime(drc[i]["ref_date"]);
                        info.branch_no = drc[i]["branch_no"].ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        //public IList<M_FIM_MAS_Currencies> GetCurrencyList(Nullable<int> money_req_seq)
        //{
        //    string strSQL;
        //    DataRowCollection drc = null;
        //    IList<M_FIM_MAS_Currencies> infoList = new List<M_FIM_MAS_Currencies>();
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(2);

        //        dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(1, "@money_req_seq", money_req_seq);

        //        strSQL = "select mrd.currency_seq, cur.currency_name_short"
        //                //+ ", coalesce(sum(mrd.amount), 0) as amount"
        //                + " from fim_money_request_dtls mrd"
        //                + " left join fim_mas_currencies cur on mrd.currency_seq = cur.currency_seq"
        //                + " where mrd.money_req_seq = @money_req_seq"
        //                + " group by mrd.currency_seq, cur.currency_name_short"

        //                //+ " union all"

        //                //+ " select red.currency_seq, cur.currency_name_short"
        //                //+ ", coalesce(sum(red.amount), 0) as amount"
        //                //+ " from fim_money_return_dtls red"
        //                //+ " left join fim_mas_currencies cur on red.currency_seq = cur.currency_seq"
        //                //+ " left join fim_money_returns re on red.return_seq = re.return_seq"
        //                //+ " where re.money_req_seq = @money_req_seq"

        //                + " union all"

        //                + " select red.to_currency_seq as currency_seq, cur.currency_name_short"
        //                //+ ", coalesce(sum(red.to_amount), 0) as amount"
        //                + " from fim_money_return_exchanges red"
        //                + " left join fim_mas_currencies cur on red.to_currency_seq = cur.currency_seq"
        //                + " left join fim_money_returns re on red.return_seq = re.return_seq"
        //                + " where re.money_req_seq = @money_req_seq"
        //                + " group by red.to_currency_seq, cur.currency_name_short";

        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            for (int i = 0; i < drc.Count; i++)
        //            {
        //                M_FIM_MAS_Currencies info = new M_FIM_MAS_Currencies();
        //                info.currency_seq = Convert.ToInt32(drc[i]["currency_seq"]);
        //                info.currency_name_short = drc[i]["currency_name_short"].ToString();
        //                infoList.Add(info);
        //            }
        //        }

        //        for (int i = 0; i < infoList.Count; i++)
        //        {
        //            strSQL = "select mrd.currency_seq"
        //                + ", sum(mrd.amount) - coalesce(ex.amount, 0) + coalesce(ex2.amount, 0) as amount"

        //                + " from fim_money_request_dtls mrd"

        //                + " left join fim_money_returns re on mrd.money_req_seq = re.money_req_seq"
        //                    + " and re.record_status not like @delete_status"
        //                + " left join (select ex.return_seq, ex.from_currency_seq"
        //                    + ", sum(ex.from_amount) as amount"
        //                    + " from fim_money_return_exchanges ex"
        //                    + " where ex.record_status not like @delete_status"
        //                    + " group by ex.return_seq, ex.from_currency_seq"
        //                    + ") ex on re.return_seq = ex.return_seq"
        //                        + " and mrd.currency_seq = ex.from_currency_seq"
        //                + " left join (select ex.return_seq, ex.to_currency_seq"
        //                    + ", sum(ex.to_amount) as amount"
        //                    + " from fim_money_return_exchanges ex"
        //                    + " where ex.record_status not like @delete_status"
        //                    + " group by ex.return_seq, ex.to_currency_seq"
        //                    + ") ex2 on re.return_seq = ex2.return_seq"
        //                        + " and mrd.currency_seq = ex2.to_currency_seq"
        //                + " where mrd.money_req_seq = @money_req_seq"
        //                + " and mrd.currency_seq = " + infoList[i].currency_seq.ToString()
        //                + " group by mrd.currency_seq";
        //            drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

        //            if (drc.Count > 0)
        //            {
        //                infoList[i].currency_name_short += " [" + Convert.ToDouble(drc[i]["amount"]).ToString("#,##0.00") + "]";
        //            }
        //            else
        //            {
        //                strSQL = "select coalesce(ex2.amount, 0) - coalesce(ex.amount, 0) as amount"

        //                + " from fim_money_returns re"
                        
        //                + " left join (select ex.return_seq, ex.from_currency_seq"
        //                    + ", sum(ex.from_amount) as amount"
        //                    + " from fim_money_return_exchanges ex"
        //                    + " where ex.record_status not like @delete_status"
        //                    + " group by ex.return_seq, ex.from_currency_seq"
        //                    + ") ex on re.return_seq = ex.return_seq"
        //                        + " and ex.currency_seq = " + infoList[i].currency_seq.ToString()
        //                + " left join (select ex.return_seq, ex.to_currency_seq"
        //                    + ", sum(ex.to_amount) as amount"
        //                    + " from fim_money_return_exchanges ex"
        //                    + " where ex.record_status not like @delete_status"
        //                    + " group by ex.return_seq, ex.to_currency_seq"
        //                    + ") ex2 on re.return_seq = ex2.return_seq"
        //                        + " and ex2.currency_seq = " + infoList[i].currency_seq.ToString()
        //                + " where re.money_req_seq = @money_req_seq";

        //                DataRowCollection drc2 = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //                if (drc2.Count > 0)
        //                {
        //                    infoList[i].currency_name_short += " [" + Convert.ToDouble(drc2[i]["amount"]).ToString("#,##0.00") + "]";
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return infoList;
        //}

        public IList<M_FIM_MoneyReturnDtls> GetList(Nullable<int> return_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyReturnDtls> infoList = new List<M_FIM_MoneyReturnDtls>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@return_seq", return_seq);

                strSQL = "SELECT red.return_dtls_seq, exp.bgm_exp_name"
                    + " FROM fim_money_return_dtls red"
                    + " LEFT JOIN fim_money_request_dtls mrd ON red.money_req_dtls_seq = mrd.money_req_dtls_seq"
                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " WHERE red.record_status not like @delete_status"
                    + (return_seq != null ? " and red.return_seq = @return_seq" : "");
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyReturnDtls info = new M_FIM_MoneyReturnDtls();
                        info.return_dtls_seq = Convert.ToInt32(drc[i]["return_dtls_seq"]);
                        info.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_MoneyReturnDtls GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MoneyReturnDtls info = new M_FIM_MoneyReturnDtls();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT red.return_dtls_seq, red.return_seq, red.money_req_dtls_seq, red.amount, red.vat_amount"

                    + ", mrd.money_req_dtls_name, mrd.amount as money_req_dtls_amount"
                    + ", exp.bgm_exp_name"

                    + ", red.record_status"
                    + ", red.created_by, red.created_when, red.updated_by, red.updated_when"
                    + ", red.ConId, red.ref_no, red.ref_date, c.NameTh, red.branch_no"
                    + " FROM fim_money_return_dtls red"
                    + " LEFT JOIN fim_money_request_dtls mrd ON red.money_req_dtls_seq = mrd.money_req_dtls_seq"
                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN IcmMasContact c on red.ConId = c.ConId"
                    + " WHERE red.return_dtls_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.return_dtls_seq = cn.ConvertNullInt(drc[0]["return_dtls_seq"]);
                    info.return_seq = cn.ConvertNullInt(drc[0]["return_seq"]);
                    info.money_req_dtls_seq = cn.ConvertNullInt(drc[0]["money_req_dtls_seq"]);
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.vat_amount = cn.ConvertNullDouble(drc[0]["vat_amount"]);

                    info.money_req_dtls_name = drc[0]["money_req_dtls_name"].ToString();
                    info.req_amount = cn.ConvertNullDouble(drc[0]["money_req_dtls_amount"]);
                    info.bgm_exp_name = drc[0]["bgm_exp_name"].ToString();

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                    info.ConId = cn.ConvertNullInt(drc[0]["ConId"]);
                    info.contact_name = drc[0]["NameTh"].ToString();
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.ref_date = cn.ConvertNullDateTime(drc[0]["ref_date"]);
                    info.branch_no = drc[0]["branch_no"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public IList<M_FIM_MoneyReturnDtls> GetList2(int money_req_dtls_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyReturnDtls> list = new List<M_FIM_MoneyReturnDtls>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@money_req_dtls_seq", money_req_dtls_seq);

                strSQL = "SELECT red.return_dtls_seq, red.return_seq, red.money_req_dtls_seq, red.amount, red.vat_amount"

                    + ", mrd.money_req_dtls_name, mrd.amount as money_req_dtls_amount"
                    + ", exp.bgm_exp_name"

                    + ", red.record_status"
                    + ", red.created_by, red.created_when, red.updated_by, red.updated_when"
                    + " FROM fim_money_return_dtls red"
                    + " LEFT JOIN fim_money_request_dtls mrd ON red.money_req_dtls_seq = mrd.money_req_dtls_seq"
                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " WHERE red.money_req_dtls_seq = @money_req_dtls_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyReturnDtls info = new M_FIM_MoneyReturnDtls
                        {
                            return_dtls_seq = cn.ConvertNullInt(drc[i]["return_dtls_seq"]),
                            return_seq = cn.ConvertNullInt(drc[i]["return_seq"]),
                            money_req_dtls_seq = cn.ConvertNullInt(drc[i]["money_req_dtls_seq"]),
                            amount = cn.ConvertNullDouble(drc[i]["amount"]),
                            vat_amount = cn.ConvertNullDouble(drc[i]["vat_amount"]),

                            money_req_dtls_name = drc[i]["money_req_dtls_name"].ToString(),
                            req_amount = cn.ConvertNullDouble(drc[i]["money_req_dtls_amount"]),
                            bgm_exp_name = drc[i]["bgm_exp_name"].ToString(),

                            record_status = drc[i]["record_status"].ToString(),
                            created_by = cn.ConvertNullInt(drc[i]["created_by"]),
                            created_when = cn.ConvertNullDateTime(drc[i]["created_when"]),
                            updated_by = cn.ConvertNullInt(drc[i]["updated_by"]),
                            updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]),
                        };

                        list.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return list;
        }

        public void Insert(M_FIM_MoneyReturnDtls info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@return_dtls_seq", info.return_dtls_seq);
                dbManager.AddParameters(1, "@return_seq", info.return_seq);
                dbManager.AddParameters(2, "@money_req_dtls_seq", info.money_req_dtls_seq);
                dbManager.AddParameters(3, "@amount", info.amount);
                dbManager.AddParameters(4, "@currency_seq", "");
                dbManager.AddParameters(5, "@exchange_rate", "");
                dbManager.AddParameters(6, "@bath_amount", "");

                dbManager.AddParameters(7, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(8, "@created_by", info.created_by);
                dbManager.AddParameters(9, "@created_when", info.created_when);
                dbManager.AddParameters(10, "@vat_amount", info.vat_amount);

                dbManager.AddParameters(11, "@ConId", info.ConId);
                dbManager.AddParameters(12, "@ref_no", info.ref_no);
                dbManager.AddParameters(13, "@ref_date", info.ref_date);
                dbManager.AddParameters(14, "@branch_no", info.branch_no);

                strSQL = "INSERT INTO fim_money_return_dtls"
                    + "(return_seq, money_req_dtls_seq, amount, vat_amount"
                    //+ ", currency_seq, exchange_rate, bath_amount"
                    + ", record_status, created_by, created_when, ConId, ref_no, ref_date, branch_no)"
                    + " VALUES (@return_seq"
                        + ", @money_req_dtls_seq"
                        + ", @amount"
                        + ", @vat_amount"
                        //+ ", @currency_seq"
                        //+ ", @exchange_rate"
                        //+ ", @bath_amount"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + ", @ConId"
                        + ", @ref_no"
                        + ", @ref_date"
                        + ", @branch_no"
                        + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_MoneyReturnDtls info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@return_dtls_seq", info.return_dtls_seq);
                dbManager.AddParameters(1, "@return_seq", info.return_seq);
                dbManager.AddParameters(2, "@money_req_dtls_seq", info.money_req_dtls_seq);
                dbManager.AddParameters(3, "@amount", info.amount);
                dbManager.AddParameters(4, "@currency_seq", "");
                dbManager.AddParameters(5, "@exchange_rate", "");
                dbManager.AddParameters(6, "@bath_amount", "");

                dbManager.AddParameters(7, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(8, "@updated_by", info.updated_by);
                dbManager.AddParameters(9, "@updated_when", info.updated_when);
                dbManager.AddParameters(10, "@vat_amount", info.vat_amount);
                dbManager.AddParameters(11, "@ConId", info.ConId);
                dbManager.AddParameters(12, "@ref_no", info.ref_no);
                dbManager.AddParameters(13, "@ref_date", info.ref_date);
                dbManager.AddParameters(14, "@branch_no", info.branch_no);

                strSQL = "UPDATE fim_money_return_dtls"
                    + " SET return_seq = @return_seq"
                    + ", money_req_dtls_seq = @money_req_dtls_seq"
                    + ", amount = @amount"
                    + ", vat_amount = @vat_amount"
                    //+ ", currency_seq = @currency_seq"
                    //+ ", exchange_rate = @exchange_rate"
                    //+ ", bath_amount = @bath_amount"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + ", ConId = @ConId"
                    + ", ref_no = @ref_no"
                    + ", ref_date = @ref_date"
                    + ", branch_no = @branch_no"
                    + " WHERE return_dtls_seq = @return_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateBathAmount(string return_dtls_seq, string bath_amount)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@return_dtls_seq", return_dtls_seq);
                dbManager.AddParameters(1, "@bath_amount", bath_amount);

                strSQL = "UPDATE fim_money_return_dtls"
                    + " SET bath_amount = @bath_amount"
                    + " WHERE return_dtls_seq = @return_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_MoneyReturnDtls info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@return_dtls_seq", info.return_dtls_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_money_return_dtls"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE return_dtls_seq = @return_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
